package dao.impl;

import dao.AdminDao;
import domain.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import util.JDBCUtils;
import util.Md5Util;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;


/**
 * @author: 爱学Java 不想秃头的 KK
 * @date: 2023/7/6 10:58
 * @version: 1.0
 * @Created with IntelliJ IDEA.
 * @Description:
 */
public class AdminDaoImpl implements AdminDao {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 查询数据库中是否有管理员账号和密码
     *
     * @param username
     * @param password
     * @return
     */
    @Override
    public Administrator findUsersByUsernameAndPassword(String username, String password) {
        Administrator admin = null;
        try {
            String sql = "select * from tab_admin where username = ? and password = ?";
            try {
                admin = template.queryForObject(
                        sql,
                        new BeanPropertyRowMapper<Administrator>(Administrator.class),
                        username,
                        Md5Util.encodeByMd5(password)
                );
            } catch (Exception e) {
                System.out.println("此管理员不存在！");
            }
        } catch (DataAccessException e) {
            System.out.println("用户不存在！" + "-------------->user/login");
        }
        return admin;
    }

    /**
     * 分页条件查询总记录数
     *
     * @param condition
     * @return
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        //1.定义模版sql
        String sql = "select count(*) from tab_user where 1 = 1 ";
        StringBuffer buffer = new StringBuffer(sql);
        //2.拼接sql语句
        Set<String> keySet = condition.keySet(); //该方法返回map中所有键的值的列表。
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            //获取value的值
            String value = condition.get(key)[0];
            //判断value中是否有值
            if (value != null && !"".equals(value)) {
                buffer.append(" and " + key + " like ?");
                params.add("%" + value + "%"); //"?"条件的值
            }
        }

        System.out.println(buffer.toString());
        System.out.println(params);

        return template.queryForObject(
                buffer.toString(),
                Integer.class, //java 1.5特性会把返回的Integer类型的数据自动拆箱为int类型
                params.toArray()
        );
    }

    /**
     * 分页条件查询每页数据
     *
     * @param start
     * @param rows
     * @param condition
     * @return
     */
    @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        //1.定义原生sql
        String sql = "select * from tab_user where 1=1 ";

        StringBuffer buffer = new StringBuffer(sql);
        //2.拼接sql语句
        Set<String> keySet = condition.keySet(); //该方法返回map中所有键的值的列表。
        //定义参数的集合
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            //获取value的值
            String value = condition.get(key)[0];
            //判断value中是否有值
            if (value != null && !"".equals(value)) {
                buffer.append(" and " + key + " like ?");
                params.add("%" + value + "%"); //"?"条件的值
            }
        }
        //添加分页查询
        buffer.append(" limit ?, ? ");

        params.add(start);
        params.add(rows);
        System.out.println(buffer.toString());
        System.out.println(params);
        return template.query(
                buffer.toString(),
                new BeanPropertyRowMapper<User>(User.class),
                params.toArray()
        );
    }


    /**
     * 删除用户
     *
     * @param uid
     */
    @Override
    public void delete(int uid) {
        String sql = "delete from tab_user where uid = ?";
        template.update(sql, uid);
    }

    /**
     * 查询文章总条数
     *
     * @return
     */
    @Override
    public int findTotalCountFromArticle() {
        String sql = "select count(*) from tab_article";
        return template.queryForObject(sql, Integer.class);
    }

    /**
     * 查询文章内容
     *
     * @param start
     * @param rows
     * @return
     */
    @Override
    public List<Article> findByPageFromArticle(int start, int rows) {
        String sql = "select * from tab_article limit ?, ?";
        return template.query(
                sql,
                new BeanPropertyRowMapper<Article>(Article.class),
                start,
                rows
        );
    }

    /**
     * 查询用户账号
     *
     * @param code
     * @return
     */
    @Override
    public User findUserByCode(String code) {
        String sql = "select *from tab_user where code = ?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), code);
    }

    /**
     * 查询文章分类
     *
     * @param cid
     * @return
     */
    @Override
    public Category findCategory(int cid) {
        try {
            String sql = "select * from tab_category where cid = ?";
            Category category = template.queryForObject(sql, new BeanPropertyRowMapper<Category>(Category.class), cid);
            return category;
        } catch (DataAccessException e) {
            System.out.println("未查询到分类.......");
            return null;
        }
    }

    @Override
    public int updateUsernameAndPassword(int id, String username, String password) {
        String sql = "UPDATE tab_admin SET username = ?, password = ? WHERE id = ?";
        PreparedStatement statement = null;
        try {
            statement = JDBCUtils.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            statement.setString(1, username);
            statement.setString(2, password);
            statement.setInt(3, id);
            //修改成功返回1
            return statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void deleteCategoryByCid(int cid) {
        String sql = "delete from tab_category where cid = ?";
        template.update(sql, cid);
    }

    @Override
    public int findTotalCountFromCategory() {
        String sql = "select count(*) from tab_category";
        return template.queryForObject(sql, Integer.class);
    }

    @Override
    public List<Category> findByPageFromCategory(int start, int rows) {
        String sql = "select * from tab_category limit ?, ?";
        return template.query(
                sql,
                new BeanPropertyRowMapper<Category>(Category.class),
                start,
                rows
        );
    }

    @Override
    public void addCategory(String cname) {
        String sql = "INSERT INTO tab_category (cname) VALUES (?)";
        template.update(sql, cname);
    }

    @Override
    public int findTotalCountFromLabel() {
        String sql = "select count(*) from tab_label";
        return template.queryForObject(sql, Integer.class);
    }

    @Override
    public List<Label> findByPageFromLabel(int start, int rows) {
        String sql = "select * from tab_label limit ?, ?";
        return template.query(
                sql,
                new BeanPropertyRowMapper<Label>(Label.class),
                start,
                rows
        );
    }

    @Override
    public void deleteLabel(int lid) {
        String sql = "delete from tab_label where lid = ?";
        template.update(sql, lid);
    }

    @Override
    public void addLabel(String lname) {
        String sql = "INSERT INTO tab_label (lname) VALUES (?)";
        template.update(sql, lname);
    }

    @Override
    public Administrator findOneAdmin(String username) {
        Administrator admin = null;
        String sql = "select * from tab_admin where username = ?";
        try {
            admin = template.queryForObject(sql, new BeanPropertyRowMapper<Administrator>(Administrator.class), username);
        } catch (DataAccessException e) {
            System.out.println("账号不存在！ -------------->admin/findOneAdmin");
        }
        return admin;
    }
}
